﻿using System.Data;
using System.Data.Common;
using Ledict.ConditionExtract.Model;
using Ledict.Core.Data;
using System.Collections.Generic;
using System;
using Ledict.ConditionExtract.Common;
using Ledict.Utility.Helper;

namespace Ledict.ConditionExtract.DAL
{
    public class AboutRegexDAL
    {
        private readonly string CommonDbName;


        private readonly string DbName;

        public AboutRegexDAL()
        {
            DbName = "JudgeDoc";
            CommonDbName = "LdtCommonManage";
        }

        public bool AddReg_InfoUnit(Reg_InfoUnit Model)
        {
            var oBase = DatabaseFactory.CreateDatabase(CommonDbName);
            var oCmd = oBase.GetStoredProcCommand("sp_CondiExt_AddReg_InfoUnit");

            oBase.AddInParameter(oCmd, "@EI_ID", DbType.String, Model.EI_ID);
            oBase.AddInParameter(oCmd, "@EI_Name", DbType.String, Model.EI_Name);
            oBase.AddInParameter(oCmd, "@EI_Code", DbType.String, Model.EI_Code);
            oBase.AddInParameter(oCmd, "@EN_ID", DbType.String, Model.EN_ID);

            oBase.AddInParameter(oCmd, "@EI_Remark", DbType.String, Model.EI_Remark);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 根据eiid删除信息单元
        /// </summary>
        /// <param name="eiID"></param>
        /// <returns></returns>
        public bool DeleteReg_InfoUnit(string eiID)
        {
            try
            {
                var oBase = DatabaseFactory.CreateDatabase(CommonDbName);
                string sql = string.Format("  delete from Reg_InfoUnit where EI_ID = '{0}' ", eiID);
                var oCmd = oBase.GetSqlStringCommand(sql);
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

           
        }

        /// <summary>
        /// 获取正则关系表内容
        /// </summary>
        /// <param name="eiId"></param>
        /// <returns></returns>
        public List<Reg_InfoRegexRelation> GetRegexRelationByEiid(string eiId)
        {
            try
            {
                List<Reg_InfoRegexRelation> rg = new List<Reg_InfoRegexRelation>();
                var oBase = DatabaseFactory.CreateDatabase(CommonDbName);
                string sql = string.Format(" select * from LdtCommonManage.dbo.Reg_InfoRegexRelation where ei_id = '{0}' ", eiId);
                var oCmd = oBase.GetSqlStringCommand(sql);
                DataTable dt = oBase.ExecuteDataSet(oCmd).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        rg.Add(new Reg_InfoRegexRelation()
                        {
                            ERR_ID = LdFormat.ObjectToString(dr["ERR_ID"]),
                            EI_ID = LdFormat.ObjectToString(dr["EI_ID"]),
                            ER_ID = LdFormat.ObjectToString(dr["ER_ID"]),
                            ERR_ShowIndex = LdFormat.ObjectToInt(dr["ERR_ShowIndex"]),
                            UpdateTime = Convert.ToDateTime(dr["UpdateTime"] == DBNull.Value ? DateTime.Now : dr["UpdateTime"])
                        });
                    }
                    return rg;
                }
                else
                { return new List<Reg_InfoRegexRelation>(); }

            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
                return new List<Reg_InfoRegexRelation>();
            }
        }

        /// <summary>
        /// 插入正则关系表内容
        /// </summary>
        /// <param name="ri"></param>
        /// <returns></returns>
        public bool InsertRegex(Reg_InfoRegexRelation ri)
        {
            try
            {
                var oBase = DatabaseFactory.CreateDatabase(CommonDbName);
                string sql = string.Format(" insert into Reg_InfoRegexRelation values('{0}','{1}','{2}','{3}','{4}') ", ri.ERR_ID, ri.EI_ID, ri.ER_ID, ri.ERR_ShowIndex, ri.UpdateTime);
                var oCmd = oBase.GetSqlStringCommand(sql);
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取正则表内容
        /// </summary>
        /// <param name="eiId"></param>
        /// <returns></returns>
        public List<Reg_Regexs> GetReg_RegexsByEiid(string eiId)
        {
            try
            {
                List<Reg_Regexs> rg = new List<Reg_Regexs>();
                var oBase = DatabaseFactory.CreateDatabase(CommonDbName);
                string sql = string.Format(" select * from LdtCommonManage.dbo.Reg_Regexs where er_id in(select er_id from LdtCommonManage.dbo.Reg_InfoRegexRelation where ei_id = '{0}')", eiId);
                var oCmd = oBase.GetSqlStringCommand(sql);
                DataTable dt = oBase.ExecuteDataSet(oCmd).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        rg.Add(new Reg_Regexs()
                        {
                            ER_ID= LdFormat.ObjectToString(dr["ER_ID"]),
                            ER_Name= LdFormat.ObjectToString(dr["ER_Name"]),
                            ER_ModifyTime=Convert.ToDateTime(dr["ER_ModifyTime"]),
                            ER_RegexText = LdFormat.ObjectToString(dr["ER_RegexText"]),
                            ER_ReplaceText = LdFormat.ObjectToString(dr["ER_ReplaceText"]),
                            ER_OperateType = LdFormat.ObjectToInt(dr["ER_OperateType"]),
                            ER_ExtractGroupIndex= LdFormat.ObjectToInt(dr["ER_ExtractGroupIndex"]),
                            ER_Remark = LdFormat.ObjectToString(dr["ER_Remark"]),
                            ER_IgnoreCase = LdFormat.ObjectToInt(dr["ER_IgnoreCase"]),
                            ER_MultiLine = LdFormat.ObjectToInt(dr["ER_MultiLine"]),
                            ER_SingleLine = LdFormat.ObjectToInt(dr["ER_SingleLine"]),
                            ER_RightToLeft = LdFormat.ObjectToInt(dr["ER_RightToLeft"]),
                            ER_ExplicitCapture = LdFormat.ObjectToInt(dr["ER_ExplicitCapture"]),
                            ER_IgnorePatternWhitespace = LdFormat.ObjectToInt(dr["ER_IgnorePatternWhitespace"]),
                            ER_ExceptString = LdFormat.ObjectToString(dr["ER_ExceptString"]),
                            ER_ParaOrSentence=LdFormat.ObjectToInt(dr["ER_ParaOrSentence"]),
                            ER_ApplyPattern = LdFormat.ObjectToInt(dr["ER_ApplyPattern"]),
                            ER_TestText = LdFormat.ObjectToString(dr["ER_TestText"]),
                            UpdateTime = Convert.ToDateTime(dr["UpdateTime"]== DBNull.Value?DateTime.Now: dr["UpdateTime"]),
                            ER_ChooseText = LdFormat.ObjectToString(dr["ER_ChooseText"])
                        });
                    }
                    return rg;
                }
                else
                { return new List<Reg_Regexs>(); }

            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
                return new List<Reg_Regexs>();
            }
        }

        /// <summary>
        /// 插入正则表内容
        /// </summary>
        /// <param name="ri"></param>
        /// <returns></returns>
        public bool InsertReg_Regexs(Reg_Regexs ri)
        {
            try
            {
                var oBase = DatabaseFactory.CreateDatabase(CommonDbName);
                ri.ER_Remark = "";
                //ER_ID, ER_Name, ER_ModifyTime, ER_RegexText, ER_ReplaceText, ER_OperateType, ER_ExtractGroupIndex, ER_Remark, ER_IgnoreCase, ER_MultiLine, ER_SingleLine, ER_RightToLeft, ER_ExplicitCapture, ER_IgnorePatternWhitespace, ER_ExceptString, ER_ParaOrSentence, ER_ApplyPattern, ER_TestText, UpdateTime, ER_ChooseText
                string sql = string.Format(" insert into Reg_Regexs values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}') ",
                    ri.ER_ID, ri.ER_Name, ri.ER_ModifyTime, ri.ER_RegexText, ri.ER_ReplaceText,ri.ER_OperateType,ri.ER_ExtractGroupIndex,ri.ER_Remark,ri.ER_IgnoreCase,ri.ER_MultiLine,ri.ER_SingleLine,ri.ER_RightToLeft,ri.ER_ExplicitCapture,ri.ER_IgnorePatternWhitespace,ri.ER_ExceptString,ri.ER_ParaOrSentence,ri.ER_ApplyPattern,ri.ER_TestText,ri.UpdateTime,ri.ER_ChooseText);
                var oCmd = oBase.GetSqlStringCommand(sql);
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #region 情形和正则关系

        /// <summary>
        ///     得到情形对应的正则ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable Relation_GetReg_EI_Code(string id)
        {
            var oBase = DatabaseFactory.CreateDatabase(DbName);
            var oCmd = oBase.GetStoredProcCommand("sp_CondiExt_Reg_GetReg_EI_Code");

            oBase.AddInParameter(oCmd, "@id", DbType.String, id);
            var OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            return new DataTable();
        }

        /// <summary>
        ///     得到情形对应的正则ID(新版，根据来源名称和段落名称进行选择，而不是单一的通过信息单元)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable Relation_GetReg_EI_Code_Sql(string id)
        {
            var oBase = DatabaseFactory.CreateDatabase(DbName);
            //            var oCmd = oBase.GetSqlStringCommand(@"
            //select   CC_RegexInfoCode,ids, b.EI_Name,InfoName  from (
            //	 
            //	 --select  CC_RegexInfoCode   from dbo.SYS_CaseCause 
            //	 --where  CC_ID=@id and ISNULL( CC_RegexInfoCode,'')<''
            //  --   union 
            //     select CC_RegexInfoCode, CC_RegexInfoCode+'-'+Source_ID+'-'+Source_ParagraphID as ids,Source_Name+'-'+Source_ParagraphName as InfoName from dbo.SYS_Express_Source as m
            //     where CC_ID=@id)
            //    as a 
            //	left join 
            // LdtCommonManage.dbo.Reg_InfoUnit   as b 
            //	on a.CC_RegexInfoCode=b.EI_Code
            //	");

            var oCmd = oBase.GetSqlStringCommand(@"
  select   CC_RegexInfoCode,ids, b.EI_Name,InfoName+'(用途:'+isnull(c.AM_Name,'')+';版本:'+ISNULL(e.AM_Name,'')+')' as InfoName,[ES_Purpose],c.AM_Name  
 from (
  select CC_RegexInfoCode, CC_RegexInfoCode+'|'+Source_ID+'|'+Source_ParagraphID+'|'+isnull([ES_Purpose],'')+'|'+CAST(Source_TextType as nvarchar) as ids,Source_Name+'-'+Source_ParagraphName as InfoName,[ES_Purpose],[Source_TextType] 
	  from dbo.SYS_Express_Source as m
     where CC_ID=@id)
    as a 
	left join "+( ConfigHelper.AppSettings("Version") == "52" ?"": "sql160.") + @" LdtCommonManage.dbo.Reg_InfoUnit   as b 	on a.CC_RegexInfoCode=b.EI_Code
	left join SYS_Argument as c	on a.[ES_Purpose] = c.AM_ID
	left join ( select AM_Code,AM_Name from [dbo].[SYS_Argument] as a  inner join [dbo].[SYS_ArgumentType] as b  on a.AT_ID = b.AT_ID and b.AT_Code = 'TextType') as e on CONVERT(varchar(100), a.[Source_TextType])=e.AM_Code
	");

            oBase.AddInParameter(oCmd, "@id", DbType.String, id);
            var otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            return new DataTable();
        }

        #endregion
    }
}